set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode='nonstrict';
set hive.exec.max.dynamic.partitions=400;
set hive.exec.max.dynamic.partitions.pernode=400;

insert overwrite table jms_dm.dm_route_whole_effect_base_dt
select
     in_from_code
    ,update_date
    ,in_from_provider_code
    ,in_from_city_code
    ,in_from_area_code
    ,out_to_provider_code
    ,out_to_city_code
    ,out_to_area_code
    ,out_to_code
    ,in_branch_id
    ,main_id
    ,out_branch_id
    ,in_collect_code
    ,in_to_code
    ,start_center_code
    ,search_type
    ,start_network_code
    ,in_from_regional_code
    ,in_from_regional_desc
    ,in_from_financial_center_code
    ,in_from_financial_center_desc
    ,in_from_provider_desc
    ,in_from_city_desc
    ,in_from_area_desc
    ,in_collect_name
    ,in_from_name
    ,in_to_name
    ,in_edge_latest_warehousing
    ,in_edge_planned_departure
    ,in_edge_planned_arrival
    ,in_edge2_planned_departure
    ,in_edge2_planned_arrival
    ,branch_in_span_days
    ,branch_in_total_time
    ,is_main_route
    ,transfer_type
    ,num_of_transfer
    ,whole_route
    ,e1_center_flow
    ,e1_line_code
    ,e1_planned_departure
    ,e1_runtime
    ,e1_planned_arrival
    ,e1_e2_stop_time
    ,e2_center_flow
    ,e2_line_code
    ,e2_planned_departure
    ,e2_runtime
    ,e2_planned_arrival
    ,e2_e3_stop_time
    ,e3_center_flow
    ,e3_line_code
    ,e3_planned_departure
    ,e3_runtime
    ,e3_planned_arrival
    ,e3_e4_stop_time
    ,e4_center_flow
    ,e4_line_code
    ,e4_planned_departure
    ,e4_runtime
    ,e4_planned_arrival
    ,e4_e5_stop_time
    ,e5_center_flow
    ,e5_line_code
    ,e5_planned_departure
    ,e5_runtime
    ,e5_planned_arrival
    ,e5_e6_stop_time
    ,e6_center_flow
    ,e6_line_code
    ,e6_planned_departure
    ,e6_runtime
    ,e6_planned_arrival
    ,e6_e7_stop_time
    ,e1_end_code
    ,e2_end_code
    ,e3_end_code
    ,e4_end_code
    ,e5_end_code
    ,e6_end_code
    ,transfer_total_span_days
    ,transfer_total_time_use
    ,main_total_span_days
    ,main_total_time_use
    ,end_center
    ,end_center_code
    ,start_center
    ,out_from_code
    ,out_collect_code
    ,out_to_regional_desc
    ,out_to_regional_code
    ,out_to_financial_center_desc
    ,out_to_financial_center_code
    ,out_to_provider_desc
    ,out_to_city_desc
    ,out_to_area_desc
    ,out_collect_name
    ,out_to_name
    ,out_from_name
    ,out_has_collect
    ,out_edge_planned_departure
    ,out_edge_planned_arrival_time
    ,out_edge2_planned_departure
    ,out_edge2_planned_arrival_time
    ,out_edge2_latest_warehouse_time
    ,out_edge_latest_warehouse_time
    ,out_edge_span_days_sign
    ,out_edge2_span_days_sign
    ,out_edge_deadline_sign_time
    ,out_edge2_deadline_sign_time
    ,branch_out_span_days
    ,branch_out_total_time
    ,effective_date
    ,expiration_date
    ,branch_in_center_stop_time
    ,center_branch_out_stop_time
    ,branch_in_center_span_days
    ,center_branch_out_span_days
    ,is_circuitous
    ,out_edge_span_days_arrive
    ,out_edge2_span_days_arrive
    ,in_nodes
    ,out_nodes
    ,total_days_use
    ,total_time_use
    ,total_days_t
    ,total_nodes
    ,working_days
    ,extra_in_collect_code
    ,extra_in_collect_name
    ,extra_in_line_name
    ,extra_in_latest_warehousing
    ,extra_in_planned_departure
    ,extra_in_planned_arrival
    ,extra_out_collect_code
    ,extra_out_collect_name
    ,extra_out_line_name
    ,extra_out_latest_warehousing
    ,extra_out_planned_departure
    ,extra_out_planned_arrival
    ,in_network_line_name
    ,in_collect_line_name
    ,out_network_line_name
    ,out_collect_line_name
    ,town_plus_time
    ,branch_in_ship_time
    ,branch_in_ship_span_days
    ,branch_out_ship_time
    ,branch_out_ship_span_days
    ,all_line_name
    ,out_town_plus_time
    ,in_edge_run_time
    ,in_edge_span_days
    ,in_edge2_run_time
    ,in_edge2_span_days
    ,in_edge3_run_time
    ,in_edge3_span_days
    ,out_edge_run_time
    ,out_edge_span_days
    ,out_edge2_run_time
    ,out_edge2_span_days
    ,out_edge3_run_time
    ,out_edge3_span_days
    ,e1_span_days
    ,e2_span_days
    ,e3_span_days
    ,e4_span_days
    ,e5_span_days
    ,e6_span_days
    ,delivery_time
    ,route_flow
    ,in_edge2_latest_warehousing
    ,e1_latest_warehousing
    ,e2_latest_warehousing
    ,e3_latest_warehousing
    ,e4_latest_arrival_time
    ,e5_latest_arrival_time
    ,e6_latest_arrival_time
    ,start_taking_shift
    ,end_send_shift
    ,warehouse_end_time
    ,reserve_1
    ,reserve_2
    ,route_contain_main_line
    ,direct_route_type
    ,in_line_day
    ,out_line_day
    ,in_ship_span_days
    ,in_manage_region_code
    ,in_manage_region_name
    ,out_manage_region_code
    ,out_manage_region_name
    ,latest_warehousing_time_quantum
    ,substr(latest_warehousing_time_quantum,7,5) as last_taking_time --揽收结束时间
    ,nvl(e1_stop_days,0) as e1_stop_days --转-转衔接跨天e1-e2
    ,nvl(e2_stop_days,0) as e2_stop_days --转-转衔接跨天e2-e3
    ,nvl(e3_stop_days,0) as e3_stop_days --转-转衔接跨天e3-e4
    ,nvl(e4_stop_days,0) as e4_stop_days --转-转衔接跨天e4-e5
    ,nvl(e5_stop_days,0) as e5_stop_days --转-转衔接跨天e5-e6
    ,nvl(e6_stop_days,0) as e6_stop_days --转-转衔接跨天e6-e7
    ,nvl(in_edge2_stop_days       ,0) as in_edge2_stop_days        --始发集散规划到车与始发集散最晚到车跨天
    ,nvl(in_edge2_stop_span_days  ,0) as in_edge2_stop_span_days   --始发集散最晚到车与始发集散规划发车跨天
    ,nvl(extra_in_stop_days       ,0) as extra_in_stop_days        --始发集散2规划到车与始发集散2最晚到车跨天
    ,nvl(extra_in_stop_span_days  ,0) as extra_in_stop_span_days   --始发集散2最晚到车与始发集散2规划发车跨天
    ,nvl(out_edge2_stop_days      ,0) as out_edge2_stop_days       --目的集散规划到车与目的集散最晚到车跨天
    ,nvl(out_edge2_stop_span_days ,0) as out_edge2_stop_span_days  --目的集散最晚到车与目的集散规划发车跨天
    ,nvl(extra_out_edge2_stop_days,0) as extra_out_edge2_stop_days --目的集散规划到车与目的集散最晚到车跨天
    ,nvl(extra_out_stop_span_days ,0) as extra_out_stop_span_days  --目的集散最晚到车与目的集散规划发车跨天
    ,branch_in_stop_days        --始发中心到车时间与最晚到车时间跨天
    ,e1_stop_stop_days          --第二中心到车时间与最晚到车时间跨天
    ,e2_stop_stop_days          --第三中心到车时间与最晚到车时间跨天
    ,e3_stop_stop_days          --第四中心到车时间与最晚到车时间跨天
    ,e4_stop_stop_days          --第五中心到车时间与最晚到车时间跨天
    ,e5_stop_stop_days          --第六中心到车时间与最晚到车时间跨天
    ,e6_stop_stop_days          --第七中心到车时间与最晚到车时间跨天
    ,branch_in_stop_span_days   --始发中心最晚到车时间与发车时间跨天
    ,e1_stop_span_days          --第二中心最晚到车时间与发车时间跨天
    ,e2_stop_span_days          --第三中心最晚到车时间与发车时间跨天
    ,e3_stop_span_days          --第四中心最晚到车时间与发车时间跨天
    ,e4_stop_span_days          --第五中心最晚到车时间与发车时间跨天
    ,e5_stop_span_days          --第六中心最晚到车时间与发车时间跨天
    ,e6_stop_span_days          --第七中心最晚到车时间与发车时间跨天
    ,dt
    ,region
from (
    select
        *,row_number() over(partition by in_from_code,in_collect_code,extra_in_collect_code,in_to_code,e1_end_code,e2_end_code,e3_end_code,e4_end_code,e5_end_code
                                        ,e6_end_code,out_from_code,out_collect_code,extra_out_collect_code,out_to_code,substr(latest_warehousing_time_quantum,7,5)
                                order by search_type asc
                                       ,network_planned_departure  desc --网点1发车时间
                                       ,nodel_f1_planed_departure  desc --集散1发车时间
                                       ,nodel_f2_planed_departure  desc --集散2发车时间
                                       ,center_f1_planed_departure desc --中心1发车时间
                                       ,center_f2_planed_departure desc --中心2发车时间
                                       ,center_f3_planed_departure desc --中心3发车时间
                                       ,center_f4_planed_departure desc --中心4发车时间
                                       ,center_f5_planed_departure desc --中心5发车时间
                                       ,center_f6_planed_departure desc --中心6发车时间
                                       ,center_f7_planed_departure desc --中心7发车时间
                                       ,nodel_l1_planed_departure  desc --目的集散1发车时间
                                       ,nodel_l2_planed_departure  desc --目的集散2发车时间
                            ) as rn
    from (
        select
             *

              ,concat(date_add('{{ execution_date | cst_ds }}',reserve_1+in_line_day),' ',in_edge_planned_departure,':00') as network_planned_departure --网点1发车时间
              ,concat(date_add('{{ execution_date | cst_ds }}',reserve_1+in_line_day+in_edge_span_days+in_edge2_stop_days+in_edge2_stop_span_days),' ',in_edge2_planned_departure,':00') as nodel_f1_planed_departure --集散1发车时间
              ,concat(date_add('{{ execution_date | cst_ds }}',reserve_1+in_line_day+in_edge_span_days+in_edge2_stop_days+in_edge2_stop_span_days+in_edge2_span_days+extra_in_stop_days+extra_in_stop_span_days),' ',extra_in_planned_departure,':00') as nodel_f2_planed_departure --集散2发车时间
              ,concat(date_add('{{ execution_date | cst_ds }}',reserve_1+in_line_day+in_edge_span_days+in_edge2_stop_days+in_edge2_stop_span_days+in_edge2_span_days+extra_in_stop_days+extra_in_stop_span_days+in_edge3_span_days+branch_in_center_span_days),' ',e1_planned_departure,':00') as center_f1_planed_departure --中心1发车时间
              ,concat(date_add('{{ execution_date | cst_ds }}',reserve_1+in_line_day+in_edge_span_days+in_edge2_stop_days+in_edge2_stop_span_days+in_edge2_span_days+extra_in_stop_days+extra_in_stop_span_days+in_edge3_span_days+branch_in_center_span_days+e1_span_days+e1_stop_days),' ',e2_planned_departure,':00') as center_f2_planed_departure --中心2发车时间
              ,concat(date_add('{{ execution_date | cst_ds }}',reserve_1+in_line_day+in_edge_span_days+in_edge2_stop_days+in_edge2_stop_span_days+in_edge2_span_days+extra_in_stop_days+extra_in_stop_span_days+in_edge3_span_days+branch_in_center_span_days+e1_span_days+e1_stop_days+e2_span_days+e2_stop_days),' ',e3_planned_departure,':00') as center_f3_planed_departure --中心3发车时间
              ,concat(date_add('{{ execution_date | cst_ds }}',reserve_1+in_line_day+in_edge_span_days+in_edge2_stop_days+in_edge2_stop_span_days+in_edge2_span_days+extra_in_stop_days+extra_in_stop_span_days+in_edge3_span_days+branch_in_center_span_days+e1_span_days+e1_stop_days+e2_span_days+e2_stop_days+e3_span_days+e3_stop_days),' ',e4_planned_departure,':00') as center_f4_planed_departure --中心4发车时间
              ,concat(date_add('{{ execution_date | cst_ds }}',reserve_1+in_line_day+in_edge_span_days+in_edge2_stop_days+in_edge2_stop_span_days+in_edge2_span_days+extra_in_stop_days+extra_in_stop_span_days+in_edge3_span_days+branch_in_center_span_days+e1_span_days+e1_stop_days+e2_span_days+e2_stop_days+e3_span_days+e3_stop_days+e4_span_days+e4_stop_days),' ',e5_planned_departure,':00') as center_f5_planed_departure --中心5发车时间
              ,concat(date_add('{{ execution_date | cst_ds }}',reserve_1+in_line_day+in_edge_span_days+in_edge2_stop_days+in_edge2_stop_span_days+in_edge2_span_days+extra_in_stop_days+extra_in_stop_span_days+in_edge3_span_days+branch_in_center_span_days+e1_span_days+e1_stop_days+e2_span_days+e2_stop_days+e3_span_days+e3_stop_days+e4_span_days+e4_stop_days+e5_span_days+e5_stop_days),' ',e6_planned_departure,':00') as center_f6_planed_departure --中心6发车时间
              ,concat(date_add('{{ execution_date | cst_ds }}',reserve_1+in_line_day+in_edge_span_days+in_edge2_stop_days+in_edge2_stop_span_days+in_edge2_span_days+extra_in_stop_days+extra_in_stop_span_days+in_edge3_span_days+branch_in_center_span_days+e1_span_days+e1_stop_days+e2_span_days+e2_stop_days+e3_span_days+e3_stop_days+e4_span_days+e4_stop_days+e5_span_days+e5_stop_days+e6_span_days+e6_stop_days+center_branch_out_span_days),' ',out_edge_planned_departure,':00') as center_f7_planed_departure --中心7发车时间
              ,concat(date_add('{{ execution_date | cst_ds }}',reserve_1+in_line_day+in_edge_span_days+in_edge2_stop_days+in_edge2_stop_span_days+in_edge2_span_days+extra_in_stop_days+extra_in_stop_span_days+in_edge3_span_days+branch_in_center_span_days+e1_span_days+e1_stop_days+e2_span_days+e2_stop_days+e3_span_days+e3_stop_days+e4_span_days+e4_stop_days+e5_span_days+e5_stop_days+e6_span_days+e6_stop_days+center_branch_out_span_days+out_edge_span_days+out_edge2_stop_days+out_edge2_stop_span_days),' ',extra_out_planned_departure,':00') as nodel_l1_planed_departure --目的集散1发车时间
              ,concat(date_add('{{ execution_date | cst_ds }}',reserve_1+in_line_day+in_edge_span_days+in_edge2_stop_days+in_edge2_stop_span_days+in_edge2_span_days+extra_in_stop_days+extra_in_stop_span_days+in_edge3_span_days+branch_in_center_span_days+e1_span_days+e1_stop_days+e2_span_days+e2_stop_days+e3_span_days+e3_stop_days+e4_span_days+e4_stop_days+e5_span_days+e5_stop_days+e6_span_days+e6_stop_days+center_branch_out_span_days+out_edge_span_days+out_edge2_stop_days+out_edge2_stop_span_days+out_edge3_span_days+extra_out_edge2_stop_days),' ',out_edge2_planned_departure,':00') as nodel_l2_planed_departure --目的集散2发车时间
        from (
            select
                 *
                ,nvl(datediff(from_unixtime(unix_timestamp(concat('{{ execution_date | cst_ds }}',' ',e1_planned_arrival,':00')) + e1_e2_stop_time * 60 ,'yyyy-MM-dd HH:mm:ss'),concat('{{ execution_date | cst_ds }}',' ',e1_planned_arrival,':00')),0) as e1_stop_days  --转-转衔接时长e1-e2
                ,nvl(datediff(from_unixtime(unix_timestamp(concat('{{ execution_date | cst_ds }}',' ',e2_planned_arrival,':00')) + e2_e3_stop_time * 60 ,'yyyy-MM-dd HH:mm:ss'),concat('{{ execution_date | cst_ds }}',' ',e2_planned_arrival,':00')),0) as e2_stop_days  --转-转衔接时长e2-e3
                ,nvl(datediff(from_unixtime(unix_timestamp(concat('{{ execution_date | cst_ds }}',' ',e3_planned_arrival,':00')) + e3_e4_stop_time * 60 ,'yyyy-MM-dd HH:mm:ss'),concat('{{ execution_date | cst_ds }}',' ',e3_planned_arrival,':00')),0) as e3_stop_days  --转-转衔接时长e3-e4
                ,nvl(datediff(from_unixtime(unix_timestamp(concat('{{ execution_date | cst_ds }}',' ',e4_planned_arrival,':00')) + e4_e5_stop_time * 60 ,'yyyy-MM-dd HH:mm:ss'),concat('{{ execution_date | cst_ds }}',' ',e4_planned_arrival,':00')),0) as e4_stop_days  --转-转衔接时长e4-e5
                ,nvl(datediff(from_unixtime(unix_timestamp(concat('{{ execution_date | cst_ds }}',' ',e5_planned_arrival,':00')) + e5_e6_stop_time * 60 ,'yyyy-MM-dd HH:mm:ss'),concat('{{ execution_date | cst_ds }}',' ',e5_planned_arrival,':00')),0) as e5_stop_days  --转-转衔接时长e5-e6
                ,nvl(datediff(from_unixtime(unix_timestamp(concat('{{ execution_date | cst_ds }}',' ',e6_planned_arrival,':00')) + e6_e7_stop_time * 60 ,'yyyy-MM-dd HH:mm:ss'),concat('{{ execution_date | cst_ds }}',' ',e6_planned_arrival,':00')),0) as e6_stop_days  --转-转衔接时长e6-e7
                ,case when in_edge_planned_arrival     > if(length(in_edge2_latest_warehousing   )>0,in_edge2_latest_warehousing,NULL) then 1 else 0 end as in_edge2_stop_days        --始发集散规划到车与始发集散最晚到车跨天
                ,case when in_edge2_latest_warehousing > if(length(in_edge2_planned_departure    )>0,in_edge2_planned_departure ,NULL) then 1 else 0 end as in_edge2_stop_span_days   --始发集散最晚到车与始发集散规划发车跨天
                ,case when in_edge2_planned_arrival    > if(length(extra_in_latest_warehousing   )>0,extra_in_latest_warehousing,NULL) then 1 else 0 end as extra_in_stop_days        --始发集散2规划到车与始发集散2最晚到车跨天
                ,case when extra_in_latest_warehousing > if(length(extra_in_planned_departure    )>0,extra_in_planned_departure ,NULL) then 1 else 0 end as extra_in_stop_span_days   --始发集散2最晚到车与始发集散2规划发车跨天
                ,case when extra_out_collect_code is null then if(out_edge_planned_arrival_time > out_edge2_latest_warehouse_time,1,0) else if(out_edge_planned_arrival_time > extra_out_latest_warehousing,1,0) end as out_edge2_stop_days   --目的集散规划到车与目的集散最晚到车跨天
                ,case when extra_out_collect_code is null then if(out_edge2_latest_warehouse_time > out_edge2_planned_departure,1,0) else if(extra_out_latest_warehousing > out_edge2_planned_departure,1,0) end as out_edge2_stop_span_days   --目的集散规划到车与目的集散最晚到车跨天
                ,case when extra_out_planned_arrival    > out_edge2_latest_warehouse_time then 1 else 0 end as extra_out_edge2_stop_days --目的集散2规划到车与目的集散2最晚到车跨天
                ,case when out_edge2_latest_warehouse_time > out_edge2_planned_departure  then 1 else 0 end as extra_out_stop_span_days  --目的集散2最晚到车与目的集散2规划发车跨天
                ,case when in_to_code  is not null and coalesce(extra_in_planned_arrival,in_edge2_planned_arrival,in_edge_planned_arrival) > coalesce(e1_latest_warehousing,out_edge_latest_warehouse_time,extra_out_latest_warehousing,out_edge2_latest_warehouse_time) then 1 else 0 end as branch_in_stop_days --始发中心到车时间与最晚到车时间跨天
                ,case when e1_end_code is not null and e1_planned_arrival > coalesce(e2_latest_warehousing ,out_edge_latest_warehouse_time,extra_out_latest_warehousing,out_edge2_latest_warehouse_time) then 1 else 0 end as e1_stop_stop_days --第二中心到车时间与最晚到车时间跨天
                ,case when e2_end_code is not null and e2_planned_arrival > coalesce(e3_latest_warehousing ,out_edge_latest_warehouse_time,extra_out_latest_warehousing,out_edge2_latest_warehouse_time) then 1 else 0 end as e2_stop_stop_days --第三中心到车时间与最晚到车时间跨天
                ,case when e3_end_code is not null and e3_planned_arrival > coalesce(e4_latest_arrival_time,out_edge_latest_warehouse_time,extra_out_latest_warehousing,out_edge2_latest_warehouse_time) then 1 else 0 end as e3_stop_stop_days --第四中心到车时间与最晚到车时间跨天
                ,case when e4_end_code is not null and e4_planned_arrival > coalesce(e5_latest_arrival_time,out_edge_latest_warehouse_time,extra_out_latest_warehousing,out_edge2_latest_warehouse_time) then 1 else 0 end as e4_stop_stop_days --第五中心到车时间与最晚到车时间跨天
                ,case when e5_end_code is not null and e5_planned_arrival > coalesce(e6_latest_arrival_time,out_edge_latest_warehouse_time,extra_out_latest_warehousing,out_edge2_latest_warehouse_time) then 1 else 0 end as e5_stop_stop_days --第六中心到车时间与最晚到车时间跨天
                ,case when e6_end_code is not null and e6_planned_arrival > coalesce(out_edge_latest_warehouse_time,extra_out_latest_warehousing,out_edge2_latest_warehouse_time) then 1 else 0 end as e6_stop_stop_days --第七中心到车时间与最晚到车时间跨天
                ,case when in_to_code  is not null and coalesce(e1_latest_warehousing ,out_edge_latest_warehouse_time,extra_out_latest_warehousing,out_edge2_latest_warehouse_time) > coalesce(e1_planned_departure,out_edge_planned_departure,extra_out_planned_departure,out_edge2_planned_departure) then 1 else 0 end as branch_in_stop_span_days --始发中心最晚到车时间与发车时间跨天
                ,case when e1_end_code is not null and coalesce(e2_latest_warehousing ,out_edge_latest_warehouse_time,extra_out_latest_warehousing,out_edge2_latest_warehouse_time) > coalesce(e2_planned_departure,out_edge_planned_departure,extra_out_planned_departure,out_edge2_planned_departure) then 1 else 0 end as e1_stop_span_days --第二中心最晚到车时间与发车时间跨天
                ,case when e2_end_code is not null and coalesce(e3_latest_warehousing ,out_edge_latest_warehouse_time,extra_out_latest_warehousing,out_edge2_latest_warehouse_time) > coalesce(e3_planned_departure,out_edge_planned_departure,extra_out_planned_departure,out_edge2_planned_departure) then 1 else 0 end as e2_stop_span_days --第三中心最晚到车时间与发车时间跨天
                ,case when e3_end_code is not null and coalesce(e4_latest_arrival_time,out_edge_latest_warehouse_time,extra_out_latest_warehousing,out_edge2_latest_warehouse_time) > coalesce(e4_planned_departure,out_edge_planned_departure,extra_out_planned_departure,out_edge2_planned_departure) then 1 else 0 end as e3_stop_span_days --第四中心最晚到车时间与发车时间跨天
                ,case when e4_end_code is not null and coalesce(e5_latest_arrival_time,out_edge_latest_warehouse_time,extra_out_latest_warehousing,out_edge2_latest_warehouse_time) > coalesce(e5_planned_departure,out_edge_planned_departure,extra_out_planned_departure,out_edge2_planned_departure) then 1 else 0 end as e4_stop_span_days --第五中心最晚到车时间与发车时间跨天
                ,case when e5_end_code is not null and coalesce(e6_latest_arrival_time,out_edge_latest_warehouse_time,extra_out_latest_warehousing,out_edge2_latest_warehouse_time) > coalesce(e6_planned_departure,out_edge_planned_departure,extra_out_planned_departure,out_edge2_planned_departure) then 1 else 0 end as e5_stop_span_days --第六中心最晚到车时间与发车时间跨天
                ,case when e6_end_code is not null and coalesce(out_edge_latest_warehouse_time,extra_out_latest_warehousing,out_edge2_latest_warehouse_time) > coalesce(out_edge_planned_departure,extra_out_planned_departure,out_edge2_planned_departure) then 1 else 0 end as e6_stop_span_days --第七中心最晚到车时间与发车时间跨天
                ,dt
                ,region
           from (
                select
                     in_from_code
                    ,update_date
                    ,in_from_provider_code
                    ,in_from_city_code
                    ,in_from_area_code
                    ,out_to_provider_code
                    ,out_to_city_code
                    ,out_to_area_code
                    ,if(length(out_to_code)>1,out_to_code,null) as out_to_code
                    ,in_branch_id
                    ,main_id
                    ,out_branch_id
                    ,if(length(in_collect_code)>1,in_collect_code,null) as in_collect_code
                    ,if(length(in_to_code)>1,in_to_code,null) as in_to_code
                    ,start_center_code
                    ,search_type
                    ,start_network_code
                    ,in_from_regional_code
                    ,in_from_regional_desc
                    ,in_from_financial_center_code
                    ,in_from_financial_center_desc
                    ,in_from_provider_desc
                    ,in_from_city_desc
                    ,in_from_area_desc
                    ,in_collect_name
                    ,in_from_name
                    ,in_to_name
                    ,in_edge_latest_warehousing
                    ,in_edge_planned_departure
                    ,in_edge_planned_arrival
                    ,in_edge2_planned_departure
                    ,in_edge2_planned_arrival
                    ,nvl(branch_in_span_days,0) as branch_in_span_days
                    ,branch_in_total_time
                    ,is_main_route
                    ,transfer_type
                    ,num_of_transfer
                    ,whole_route
                    ,if(length(e1_center_flow      )>0,e1_center_flow       ,null) as e1_center_flow
                    ,if(length(e1_line_code        )>0,e1_line_code         ,null) as e1_line_code
                    ,if(length(e1_planned_departure)>0,e1_planned_departure ,null) as e1_planned_departure
                    ,if(length(e1_runtime          )>0,e1_runtime           ,null) as e1_runtime
                    ,if(length(e1_planned_arrival  )>0,e1_planned_arrival   ,null) as e1_planned_arrival
                    ,if(length(e1_e2_stop_time     )>0,e1_e2_stop_time      ,null) as e1_e2_stop_time
                    ,if(length(e2_center_flow      )>0,e2_center_flow       ,null) as e2_center_flow
                    ,if(length(e2_line_code        )>0,e2_line_code         ,null) as e2_line_code
                    ,if(length(e2_planned_departure)>0,e2_planned_departure ,null) as e2_planned_departure
                    ,if(length(e2_runtime          )>0,e2_runtime           ,null) as e2_runtime
                    ,if(length(e2_planned_arrival  )>0,e2_planned_arrival   ,null) as e2_planned_arrival
                    ,if(length(e2_e3_stop_time     )>0,e2_e3_stop_time      ,null) as e2_e3_stop_time
                    ,if(length(e3_center_flow      )>0,e3_center_flow       ,null) as e3_center_flow
                    ,if(length(e3_line_code        )>0,e3_line_code         ,null) as e3_line_code
                    ,if(length(e3_planned_departure)>0,e3_planned_departure ,null) as e3_planned_departure
                    ,if(length(e3_runtime          )>0,e3_runtime           ,null) as e3_runtime
                    ,if(length(e3_planned_arrival  )>0,e3_planned_arrival   ,null) as e3_planned_arrival
                    ,if(length(e3_e4_stop_time     )>0,e3_e4_stop_time      ,null) as e3_e4_stop_time
                    ,if(length(e4_center_flow      )>0,e4_center_flow       ,null) as e4_center_flow
                    ,if(length(e4_line_code        )>0,e4_line_code         ,null) as e4_line_code
                    ,if(length(e4_planned_departure)>0,e4_planned_departure ,null) as e4_planned_departure
                    ,if(length(e4_runtime          )>0,e4_runtime           ,null) as e4_runtime
                    ,if(length(e4_planned_arrival  )>0,e4_planned_arrival   ,null) as e4_planned_arrival
                    ,if(length(e4_e5_stop_time     )>0,e4_e5_stop_time      ,null) as e4_e5_stop_time
                    ,if(length(e5_center_flow      )>0,e5_center_flow       ,null) as e5_center_flow
                    ,if(length(e5_line_code        )>0,e5_line_code         ,null) as e5_line_code
                    ,if(length(e5_planned_departure)>0,e5_planned_departure ,null) as e5_planned_departure
                    ,if(length(e5_runtime          )>0,e5_runtime           ,null) as e5_runtime
                    ,if(length(e5_planned_arrival  )>0,e5_planned_arrival   ,null) as e5_planned_arrival
                    ,if(length(e5_e6_stop_time     )>0,e5_e6_stop_time      ,null) as e5_e6_stop_time
                    ,if(length(e6_center_flow      )>0,e6_center_flow       ,null) as e6_center_flow
                    ,if(length(e6_line_code        )>0,e6_line_code         ,null) as e6_line_code
                    ,if(length(e6_planned_departure)>0,e6_planned_departure ,null) as e6_planned_departure
                    ,if(length(e6_runtime          )>0,e6_runtime           ,null) as e6_runtime
                    ,if(length(e6_planned_arrival  )>0,e6_planned_arrival   ,null) as e6_planned_arrival
                    ,if(length(e6_e7_stop_time     )>0,e6_e7_stop_time      ,null) as e6_e7_stop_time
                    ,if(length(e1_end_code         )>0,e1_end_code          ,null) as e1_end_code
                    ,if(length(e2_end_code         )>0,e2_end_code          ,null) as e2_end_code
                    ,if(length(e3_end_code         )>0,e3_end_code          ,null) as e3_end_code
                    ,if(length(e4_end_code         )>0,e4_end_code          ,null) as e4_end_code
                    ,if(length(e5_end_code         )>0,e5_end_code          ,null) as e5_end_code
                    ,if(length(e6_end_code         )>0,e6_end_code          ,null) as e6_end_code
                    ,transfer_total_span_days
                    ,transfer_total_time_use
                    ,main_total_span_days
                    ,main_total_time_use
                    ,end_center
                    ,end_center_code
                    ,start_center
                    ,if(length(out_from_code)>1,out_from_code,null) as out_from_code
                    ,if(length(out_collect_code)>1,out_collect_code,null) as out_collect_code
                    ,out_to_regional_desc
                    ,out_to_regional_code
                    ,out_to_financial_center_desc
                    ,out_to_financial_center_code
                    ,out_to_provider_desc
                    ,out_to_city_desc
                    ,out_to_area_desc
                    ,out_collect_name
                    ,out_to_name
                    ,out_from_name
                    ,out_has_collect
                    ,out_edge_planned_departure
                    ,out_edge_planned_arrival_time
                    ,if(length(out_edge2_planned_departure)>1,out_edge2_planned_departure,null) as out_edge2_planned_departure
                    ,out_edge2_planned_arrival_time
                    ,if(length(out_edge2_latest_warehouse_time)>1,out_edge2_latest_warehouse_time,null) as out_edge2_latest_warehouse_time
                    ,if(length(out_edge_latest_warehouse_time)>1,out_edge_latest_warehouse_time,null) as out_edge_latest_warehouse_time
                    ,out_edge_span_days_sign
                    ,out_edge2_span_days_sign
                    ,out_edge_deadline_sign_time
                    ,out_edge2_deadline_sign_time
                    ,nvl(branch_out_span_days,0) as branch_out_span_days
                    ,branch_out_total_time
                    ,effective_date
                    ,expiration_date
                    ,branch_in_center_stop_time
                    ,center_branch_out_stop_time
                    ,nvl(branch_in_center_span_days,0) as branch_in_center_span_days
                    ,nvl(center_branch_out_span_days,0) as center_branch_out_span_days
                    ,is_circuitous
                    ,out_edge_span_days_arrive
                    ,out_edge2_span_days_arrive
                    ,in_nodes
                    ,out_nodes
                    ,total_days_use
                    ,total_time_use
                    ,total_days_t
                    ,total_nodes
                    ,working_days
                    ,if(length(extra_in_collect_code       )>0,extra_in_collect_code       ,null) as extra_in_collect_code
                    ,if(length(extra_in_collect_name       )>0,extra_in_collect_name       ,null) as extra_in_collect_name
                    ,if(length(extra_in_line_name          )>0,extra_in_line_name          ,null) as extra_in_line_name
                    ,if(length(extra_in_latest_warehousing )>0,extra_in_latest_warehousing ,null) as extra_in_latest_warehousing
                    ,if(length(extra_in_planned_departure  )>0,extra_in_planned_departure  ,null) as extra_in_planned_departure
                    ,if(length(extra_in_planned_arrival    )>0,extra_in_planned_arrival    ,null) as extra_in_planned_arrival
                    ,if(length(extra_out_collect_code      )>0,extra_out_collect_code      ,null) as extra_out_collect_code
                    ,if(length(extra_out_collect_name      )>0,extra_out_collect_name      ,null) as extra_out_collect_name
                    ,if(length(extra_out_line_name         )>0,extra_out_line_name         ,null) as extra_out_line_name
                    ,if(length(extra_out_latest_warehousing)>0,extra_out_latest_warehousing,null) as extra_out_latest_warehousing
                    ,if(length(extra_out_planned_departure )>0,extra_out_planned_departure ,null) as extra_out_planned_departure
                    ,if(length(extra_out_planned_arrival   )>0,extra_out_planned_arrival   ,null) as extra_out_planned_arrival
                    ,if(length(in_network_line_name        )>0,in_network_line_name        ,null) as in_network_line_name
                    ,if(length(in_collect_line_name        )>0,in_collect_line_name        ,null) as in_collect_line_name
                    ,if(length(out_network_line_name       )>0,out_network_line_name       ,null) as out_network_line_name
                    ,if(length(out_collect_line_name       )>0,out_collect_line_name       ,null) as out_collect_line_name
                    ,town_plus_time
                    ,branch_in_ship_time
                    ,branch_in_ship_span_days
                    ,branch_out_ship_time
                    ,branch_out_ship_span_days
                    ,all_line_name
                    ,out_town_plus_time
                    ,in_edge_run_time
                    ,nvl(in_edge_span_days,0) as in_edge_span_days
                    ,in_edge2_run_time
                    ,nvl(in_edge2_span_days,0) as in_edge2_span_days
                    ,in_edge3_run_time
                    ,nvl(in_edge3_span_days,0) as in_edge3_span_days
                    ,out_edge_run_time
                    ,nvl(out_edge_span_days,0) as out_edge_span_days
                    ,out_edge2_run_time
                    ,nvl(out_edge2_span_days,0) as out_edge2_span_days
                    ,out_edge3_run_time
                    ,nvl(out_edge3_span_days,0) as out_edge3_span_days
                    ,nvl(e1_span_days,0) as e1_span_days
                    ,nvl(e2_span_days,0) as e2_span_days
                    ,nvl(e3_span_days,0) as e3_span_days
                    ,nvl(e4_span_days,0) as e4_span_days
                    ,nvl(e5_span_days,0) as e5_span_days
                    ,nvl(e6_span_days,0) as e6_span_days
                    ,delivery_time
                    ,route_flow
                    ,if(length(in_edge2_latest_warehousing )>0,in_edge2_latest_warehousing ,null) as in_edge2_latest_warehousing
                    ,if(length(e1_latest_warehousing       )>0,e1_latest_warehousing       ,null) as e1_latest_warehousing
                    ,if(length(e2_latest_warehousing       )>0,e2_latest_warehousing       ,null) as e2_latest_warehousing
                    ,if(length(e3_latest_warehousing       )>0,e3_latest_warehousing       ,null) as e3_latest_warehousing
                    ,if(length(e4_latest_arrival_time      )>0,e4_latest_arrival_time      ,null) as e4_latest_arrival_time
                    ,if(length(e5_latest_arrival_time      )>0,e5_latest_arrival_time      ,null) as e5_latest_arrival_time
                    ,if(length(e6_latest_arrival_time      )>0,e6_latest_arrival_time      ,null) as e6_latest_arrival_time
                    ,start_taking_shift
                    ,end_send_shift
                    ,warehouse_end_time
                    ,nvl(reserve_1,0) as reserve_1
                    ,nvl(reserve_2,0) as reserve_2
                    ,route_contain_main_line
                    ,direct_route_type
                    ,nvl(in_line_day,0) as in_line_day
                    ,nvl(out_line_day,0) as out_line_day
                    ,nvl(in_ship_span_days,0) as in_ship_span_days
                    ,in_manage_region_code
                    ,in_manage_region_name
                    ,out_manage_region_code
                    ,out_manage_region_name
                    ,latest_warehousing_time_quantum
                    ,substr(latest_warehousing_time_quantum,7,5) as last_taking_time --揽收结束时间
                    ,dt
                    ,region
                from (
                    select
                         in_from_code
                        ,update_date
                        ,in_from_provider_code
                        ,in_from_city_code
                        ,in_from_area_code
                        ,out_to_provider_code
                        ,out_to_city_code
                        ,out_to_area_code
                        ,out_to_code
                        ,in_branch_id
                        ,main_id
                        ,out_branch_id
                        ,in_collect_code
                        ,in_to_code
                        ,start_center_code
                        ,search_type
                        ,start_network_code
                        ,in_from_regional_code
                        ,in_from_regional_desc
                        ,in_from_financial_center_code
                        ,in_from_financial_center_desc
                        ,in_from_provider_desc
                        ,in_from_city_desc
                        ,in_from_area_desc
                        ,in_collect_name
                        ,in_from_name
                        ,in_to_name
                        ,in_edge_latest_warehousing
                        ,in_edge_planned_departure
                        ,in_edge_planned_arrival
                        ,in_edge2_planned_departure
                        ,in_edge2_planned_arrival
                        ,branch_in_span_days
                        ,branch_in_total_time
                        ,is_main_route
                        ,transfer_type
                        ,num_of_transfer
                        ,whole_route
                        ,e1_center_flow
                        ,e1_line_code
                        ,e1_planned_departure
                        ,e1_runtime
                        ,e1_planned_arrival
                        ,if(length(e2_end_code) > 0,e1_e2_stop_time,0) as e1_e2_stop_time
                        ,e2_center_flow
                        ,e2_line_code
                        ,e2_planned_departure
                        ,e2_runtime
                        ,e2_planned_arrival
                        ,if(length(e3_end_code) > 0,e2_e3_stop_time,0) as e2_e3_stop_time
                        ,e3_center_flow
                        ,e3_line_code
                        ,e3_planned_departure
                        ,e3_runtime
                        ,e3_planned_arrival
                        ,if(length(e4_end_code) > 0,e3_e4_stop_time,0) as e3_e4_stop_time
                        ,e4_center_flow
                        ,e4_line_code
                        ,e4_planned_departure
                        ,e4_runtime
                        ,e4_planned_arrival
                        ,if(length(e5_end_code) > 0,e4_e5_stop_time,0) as e4_e5_stop_time
                        ,e5_center_flow
                        ,e5_line_code
                        ,e5_planned_departure
                        ,e5_runtime
                        ,e5_planned_arrival
                        ,if(length(e6_end_code) > 0,e5_e6_stop_time,0) as e5_e6_stop_time
                        ,e6_center_flow
                        ,e6_line_code
                        ,e6_planned_departure
                        ,e6_runtime
                        ,e6_planned_arrival
                        ,e6_e7_stop_time --不需要判断,不存在这种场景
                        ,e1_end_code
                        ,e2_end_code
                        ,e3_end_code
                        ,e4_end_code
                        ,e5_end_code
                        ,e6_end_code
                        ,transfer_total_span_days
                        ,transfer_total_time_use
                        ,main_total_span_days
                        ,main_total_time_use
                        ,end_center
                        ,end_center_code
                        ,start_center
                        ,out_from_code
                        ,out_collect_code
                        ,out_to_regional_desc
                        ,out_to_regional_code
                        ,out_to_financial_center_desc
                        ,out_to_financial_center_code
                        ,out_to_provider_desc
                        ,out_to_city_desc
                        ,out_to_area_desc
                        ,out_collect_name
                        ,out_to_name
                        ,out_from_name
                        ,out_has_collect
                        ,out_edge_planned_departure
                        ,out_edge_planned_arrival_time
                        ,out_edge2_planned_departure
                        ,out_edge2_planned_arrival_time
                        ,out_edge2_latest_warehouse_time
                        ,out_edge_latest_warehouse_time
                        ,out_edge_span_days_sign
                        ,out_edge2_span_days_sign
                        ,out_edge_deadline_sign_time
                        ,out_edge2_deadline_sign_time
                        ,branch_out_span_days
                        ,branch_out_total_time
                        ,effective_date
                        ,expiration_date
                        ,branch_in_center_stop_time
                        ,center_branch_out_stop_time
                        ,branch_in_center_span_days
                        ,center_branch_out_span_days
                        ,is_circuitous
                        ,out_edge_span_days_arrive
                        ,out_edge2_span_days_arrive
                        ,in_nodes
                        ,out_nodes
                        ,total_days_use
                        ,total_time_use
                        ,total_days_t
                        ,total_nodes
                        ,working_days
                        ,extra_in_collect_code
                        ,extra_in_collect_name
                        ,extra_in_line_name
                        ,extra_in_latest_warehousing
                        ,extra_in_planned_departure
                        ,extra_in_planned_arrival
                        ,extra_out_collect_code
                        ,extra_out_collect_name
                        ,extra_out_line_name
                        ,extra_out_latest_warehousing
                        ,extra_out_planned_departure
                        ,extra_out_planned_arrival
                        ,in_network_line_name
                        ,in_collect_line_name
                        ,out_network_line_name
                        ,out_collect_line_name
                        ,town_plus_time
                        ,branch_in_ship_time
                        ,branch_in_ship_span_days
                        ,branch_out_ship_time
                        ,branch_out_ship_span_days
                        ,all_line_name
                        ,out_town_plus_time
                        ,in_edge_run_time
                        ,in_edge_span_days
                        ,in_edge2_run_time
                        ,in_edge2_span_days
                        ,in_edge3_run_time
                        ,in_edge3_span_days
                        ,out_edge_run_time
                        ,out_edge_span_days
                        ,out_edge2_run_time
                        ,out_edge2_span_days
                        ,out_edge3_run_time
                        ,out_edge3_span_days
                        ,e1_span_days
                        ,e2_span_days
                        ,e3_span_days
                        ,e4_span_days
                        ,e5_span_days
                        ,e6_span_days
                        ,delivery_time
                        ,route_flow
                        ,in_edge2_latest_warehousing
                        ,e1_latest_warehousing
                        ,e2_latest_warehousing
                        ,e3_latest_warehousing
                        ,e4_latest_arrival_time
                        ,e5_latest_arrival_time
                        ,e6_latest_arrival_time
                        ,start_taking_shift
                        ,end_send_shift
                        ,warehouse_end_time
                        ,reserve_1
                        ,reserve_2
                        ,route_contain_main_line
                        ,direct_route_type
                        ,in_line_day
                        ,out_line_day
                        ,in_ship_span_days
                        ,in_manage_region_code
                        ,in_manage_region_name
                        ,out_manage_region_code
                        ,out_manage_region_name
                        ,latest_warehousing_time_quantum
                        ,dt
                        ,region
                    from jms_dm.dm_route_whole_base_dt
                    where dt = '{{ execution_date | cst_ds }}'
                    and in_from_name is not null
                    and out_to_code is not null
                    and is_main_route <> 2 --剔除航空路由
                    and nvl(route_contain_main_line,0) <> 1 --剔除直发
                    and in_branch_id is not null --剔除直发
                    and latest_warehousing_time_quantum <> '00:00-00:01'
                    and region not in ('huanan','huadong')
                    and (out_collect_line_name <>'成都-拉萨集散点1201(2班)' or out_collect_line_name is null)
                ) a
            ) a
        ) a
    ) a
) a  where a.rn = 1
distribute by pmod(hash(rand()), 200);

